package quanlyhochieu.taomoi;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Date;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.faces.model.SelectItem;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import org.apache.myfaces.custom.fileupload.UploadedFile;

import dbutil.ConnectDB;

import quanlydoanra.utils.managerID;
import quanlydoanra.utils.truyXuatDatabase;
import utils.DateUtils;
import utils.UtilsOnScopes;

public class HoChieu {
	private String soHC = null;
	private String loaiHC = null;
	private String ngayCap =null ;
	private String ngayHetHan = null;
	private String noiCap = null;
	private String cmnd = null;
	private String ten = null;
	private String namSinh =null;
	private String gioiTinh = null;
	private String coQuan =null;
	private String fileAnh = null;
	private boolean dangVien = false;
	private String chucVu =null;
	private String error =null;
	private SelectItem[] listGioiTinh =null;
	private SelectItem[] listLoaiHC = null;
	private SelectItem[] listTrangThaiTraNhan = null;
	private String maTu = null;
	private String nganTu = null;
	private String trangThaiHC =null;
	private String moTa = null;
	private SelectItem[] listNganTu = null;
	private SelectItem[] listTuDungHC = null;
	
	private List<HoChieu> listHC = null;
	private String stt = null;
	private java.util.Date dateNgayCap;
	private java.util.Date dateNgayHetHan;
	private int idViTriLuuTru;
	
	
	
	public String getMoTa() {
		return moTa;
	}

	public void setMoTa(String moTa) {
		this.moTa = moTa;		
	}
	
	public SelectItem[] getListNganTu() {
		if (listNganTu==null){
			this.listNganTu = loadFirstItem();
		}
		return this.listNganTu;
	}

	public void setListNganTu(SelectItem[] listNganTu) {
		this.listNganTu = listNganTu;
	}

	public SelectItem[] getListTuDungHC() {
		return listTuDungHC;
	}

	public void setListTuDungHC(SelectItem[] listTuDungHC) {
		this.listTuDungHC = listTuDungHC;
	}

	public String getError() {
		return error;
	}

	public void setError(String error) {
		this.error = error;
	}

	public HoChieu(){}

	public String getSoHC() {
		return soHC;
	}

	public void setSoHC(String soHC) {
		this.soHC = soHC;
	}

	public String getLoaiHC() {
		return loaiHC;
	}

	public void setLoaiHC(String loaiHC) {
		this.loaiHC = loaiHC;
	}

	public String getNgayCap() {
		this.ngayCap = DateUtils.LayNgayThangHienTai();
		return ngayCap;
	}

	public void setNgayCap(String ngayCap) {
		this.ngayCap = ngayCap;
	}

	public String getNgayHetHan() {
		int month = DateUtils.LayThangHienTai();
		int day = DateUtils.LayNgayHienTai();
		int year = DateUtils.LayNamHienTai()+5;
		String strNgayHetHan = day+"/"+month+"/"+year;
		this.ngayHetHan = strNgayHetHan;
			return ngayHetHan;
	}

	public void setNgayHetHan(String ngayHetHan) {
		this.ngayHetHan = ngayHetHan;
	}

	public String getNoiCap() {
		return noiCap;
	}

	public void setNoiCap(String noiCap) {
		this.noiCap = noiCap;
	}

	public String getCmnd() {
		return cmnd;
	}

	public void setCmnd(String cmnd) {
		this.cmnd = cmnd;
	}

	public String getTen() {
		return ten;
	}

	public void setTen(String ten) {
		this.ten = ten;
	}

	public String getNamSinh() {
		return namSinh;
	}

	public void setNamSinh(String namSinh) {
		this.namSinh = namSinh;
	}

	public String getGioiTinh() {
		return gioiTinh;
	}
	public SelectItem[] getListGioiTinh() {
		SelectItem[] arrItem = new SelectItem[2];
		arrItem[0] = new SelectItem("Nam", "Nam");
		arrItem[1] = new SelectItem("Nữ", "Nữ");
		
		return arrItem;
	}
	
	public void setListGioiTinh(SelectItem[] listGioiTinh) {
		this.listGioiTinh = listGioiTinh;
	}
	public SelectItem[] getListTrangThaiTraNhan() {
		SelectItem[] arrItem = new SelectItem[2];
		arrItem[0] = new SelectItem("Giao", "Giao");
		arrItem[1] = new SelectItem("Nhận", "Nhận");
		
		return arrItem;	
	}

	public void setListTrangThaiTraNhan(SelectItem[] listTrangThaiTraNhan) {
		this.listTrangThaiTraNhan = listTrangThaiTraNhan;
	}
	public void setGioiTinh(String gioiTinh) {
		this.gioiTinh = gioiTinh;
	}

	public String getCoQuan() {
		return coQuan;
	}

	public void setCoQuan(String coQuan) {
		this.coQuan = coQuan;
	}
	
	
	public String getFileAnh() {
		return fileAnh;
	}
	
	public void setFileAnh(String fileAnh) {
		this.fileAnh = fileAnh;
	}
	

	public boolean isDangVien() {
		return dangVien;
	}

	public void setDangVien(boolean dangVien) {
		this.dangVien = dangVien;
	}

	public String getChucVu() {
		return chucVu;
	}

	public void setChucVu(String chucVu) {
		this.chucVu = chucVu;
	}

	public String getMaTu() {
		return maTu;
	}

	public void setMaTu(String maTu) {
		this.maTu = maTu;
	}

	public String getNganTu() {
		return nganTu;
	}

	public void setNganTu(String nganTu) {
		this.nganTu = nganTu;
	}

	public String getTrangThaiHC() {
		return trangThaiHC;
	}

	public void setTrangThaiHC(String trangThaiHC) {
		this.trangThaiHC = trangThaiHC;
	}
	public String getFileImage(){
		System.out.println("Vao file anh ");	  
		return "finish_them_hochieu";
	}
	
	/**************************************
	 * Method name		: distincMucDich
	 * Return type		: boolean
	 * Description		:
	 * Created date		: Aug 13, 2008
	 * Author			: Ngoc Diem
	 **************************************/
	public boolean distincViTriLuuTru(List<ViTriLuuTru> listViTriLuuTru,ViTriLuuTru m){
		boolean result = true;
		for (ViTriLuuTru l : listViTriLuuTru) {
			if(l.getNganTrongTu().equals(m.getNganTrongTu())){
				result = false;
				break;
			}			
		}
		return result;
	}
	
	/**************************************
	 * Method name		: dsViTriLuuTru
	 * Return type		: List<ViTriLuuTru>
	 * Description		:
	 * Created date		: Aug 20, 2008
	 * Author			: Ngoc Diem
	 **************************************/
	public  List<ViTriLuuTru> dsViTriLuuTru(){
		List<ViTriLuuTru> listViTriLuu = new ArrayList<ViTriLuuTru>();
		try{
		String SQL = "SELECT DISTINCT * FROM kholuutru where vitritu ='"+this.maTu+"'" ;
		ArrayList array = ConnectDB.getDataTypeList(SQL);
			if (array.size() > 0) {
			HashMap<String, Object> hash = new HashMap<String, Object>();
			for (int i = 0; i < array.size(); i++) {
				ViTriLuuTru MD = new ViTriLuuTru();
				hash = (HashMap<String, Object>) array.get(i);
				MD.setId(Integer.parseInt(hash.get("id").toString()));
				MD.setNganTrongTu(hash.get("ngantrongtu").toString());
				if(this.distincViTriLuuTru(listViTriLuu,MD) == true)
					listViTriLuu.add(MD);
			}
			System.out.println("SQL muc dich "+SQL);
		}
		}
		catch(Exception ex){
			System.out.println("Loi");
		}
		return listViTriLuu;
	}
	
	/**************************************
	 * Method name		: listItemMucDich
	 * Return type		: SelectItem[]
	 * Description		:
	 * Created date		: Aug 12, 2008
	 * Author			: Ngoc Diem
	 **************************************/
	public SelectItem[] listItemViTriLuu() {
		List<ViTriLuuTru> listFunct = this.dsViTriLuuTru();
		System.out.println("Vao list item muc dich");
		SelectItem[] arrItem = new SelectItem[listFunct.size()];
		for (int i = 0; i < listFunct.size(); i++) {
			ViTriLuuTru FD = listFunct.get(i);
			arrItem[i] = new SelectItem(FD.getNganTrongTu(), FD.getNganTrongTu());
		}
		return arrItem;
	}
	/**************************************
	 * Method name		: loadFirstItem
	 * Return type		: SelectItem[]
	 * Description		:
	 * Created date		: Aug 12, 2008
	 * Author			: Ngoc Diem
	 **************************************/
	public SelectItem[] loadFirstItem(){
		List<ViTriLuuTru> listViTriLuuTru = new ArrayList<ViTriLuuTru>();
		try{
		String SQL = "SELECT DISTINCT  * FROM kholuutru where vitritu ='Tủ A'" ;
		ArrayList array = ConnectDB.getDataTypeList(SQL);
		if (array.size() > 0) {
			HashMap<String, Object> hash = new HashMap<String, Object>();
			for (int i = 0; i < array.size(); i++) {
				ViTriLuuTru MD = new ViTriLuuTru();
				hash = (HashMap<String, Object>) array.get(i);
				MD.setId(Integer.parseInt(hash.get("id").toString()));
				MD.setNganTrongTu(hash.get("ngantrongtu").toString());
				if(this.distincViTriLuuTru(listViTriLuuTru,MD) == true)
					listViTriLuuTru.add(MD);
			}
		}
		}
		catch(Exception ex){
			System.out.println("Loi");
		}
	
		SelectItem[] arrItem = new SelectItem[listViTriLuuTru.size()];
		for (int i = 0; i < listViTriLuuTru.size(); i++) {
			ViTriLuuTru FD = listViTriLuuTru.get(i);
			arrItem[i] = new SelectItem(FD.getNganTrongTu(), FD.getNganTrongTu());
		}
		return arrItem;
	}
		
	/**************************************
	 * Method name		: locTheoTuDungHC
	 * Return type		: String
	 * Description		:
	 * Created date		: Aug 13, 2008
	 * Author			: Ngoc Diem
	 **************************************/
	public String locTheoTuDungHC(){
		SelectItem[] select = this.listItemViTriLuu();
		this.setListNganTu(select);
		return "finish_them_hochieu";
		
	}
	
	public String locTheoTuDungHCSua(){
		SelectItem[] select = this.listItemViTriLuu();
		this.setListNganTu(select);
		return "finish_sua_hochieu";
		
	}
	public boolean checkCMNDInDatabase(){
		boolean result = false;
		String sql = "select * from canhan where cmnd = '"+this.cmnd+"'";
		ArrayList array = ConnectDB.getDataTypeList(sql);
		if (array.size() > 0) {
			result = true;
		}
		return result;
	}
	public String checkCMND(){
		String sql = "select * from canhan as CN, hochieu as HC where CN.cmnd = HC.cmnd and  CN.cmnd = '"+this.cmnd+"'";
		ArrayList array = ConnectDB.getDataTypeList(sql);
		if (array.size() > 0) {
			HashMap<String, Object> hash = new HashMap<String, Object>();
			hash = (HashMap<String, Object>) array.get(0);
			String strTen = hash.get("ten").toString();
			String strcmnd = hash.get("cmnd").toString();	
			String strcoquan = hash.get("coquan").toString();
			String strgioitinh = hash.get("gioitinh").toString();
			String strNamSinh = hash.get("namsinh").toString();
			String gioitinh ="";
			boolean b = Boolean.parseBoolean(strgioitinh);
			if (b == true) gioitinh = "Nam";
			else gioitinh = "Nữ";
			String strLoaiHC =  hash.get("loaihc").toString();
			this.setTen(strTen);
			this.setCmnd(strcmnd);
			this.setNamSinh(strNamSinh);
			this.setGioiTinh(gioitinh);
			this.setCoQuan(strcoquan);
			this.setChucVu(hash.get("chucvu").toString());
			this.setDangVien(Boolean.parseBoolean(hash.get("dangvien").toString()));
			this.setError("Thành viên này đã tạo hộ chiếu "+strLoaiHC+" với số hộ chiếu là "+hash.get("sohc").toString());
			if(array.size() == 1 ){
				if(strLoaiHC.equals("Phổ Thông")){
				SelectItem[] arrItem = new SelectItem[2];
				arrItem[0] = new SelectItem("Công Vụ", "Công Vụ");
				arrItem[1] = new SelectItem("Ngoại Giao", "Ngoại Giao");
				this.setListLoaiHC(arrItem);
				}
				else if(strLoaiHC.equals("Công Vụ")){
					SelectItem[] arrItem = new SelectItem[2];
					arrItem[0] = new SelectItem("Phổ Thông", "Phổ Thông");
					arrItem[1] = new SelectItem("Ngoại Giao", "Ngoại Giao");
					this.setListLoaiHC(arrItem);
				}
				else if(strLoaiHC.equals("Ngoại Giao")){
					SelectItem[] arrItem = new SelectItem[2];
					arrItem[0] = new SelectItem("Phổ Thông", "Phổ Thông");
					arrItem[1] = new SelectItem("Công Vụ", "Công Vụ");
					this.setListLoaiHC(arrItem);
				}				
			}
			else if(array.size() == 2){
				HashMap<String, Object>	hash1 = (HashMap<String, Object>) array.get(1);
				if(strLoaiHC.equals("Công Vụ") && hash1.get("loaihc").toString().equals("Ngoại Giao")){
					SelectItem[] arrItem = new SelectItem[1];
					arrItem[0] = new SelectItem("Phổ Thông", "Phổ Thông");
					this.setListLoaiHC(arrItem);
				}
				else if(strLoaiHC.equals("Công Vụ") && hash1.get("loaihc").toString().equals("Phổ Thông")){
					SelectItem[] arrItem = new SelectItem[1];
					arrItem[0] = new SelectItem("Ngoại Giao", "Ngoại Giao");
					this.setListLoaiHC(arrItem);
				}
				else if(strLoaiHC.equals("Phổ Thông") && hash1.get("loaihc").toString().equals("Ngoại Giao")){
					SelectItem[] arrItem = new SelectItem[1];
					arrItem[0] = new SelectItem("Công Vụ", "Công Vụ");
					this.setListLoaiHC(arrItem);
				}
			}
			else if(array.size() == 3){
				SelectItem[] arrItem = new SelectItem[0];
				this.setListLoaiHC(arrItem);
			}
			else {
				SelectItem[] arrItem = new SelectItem[3];
				arrItem[0] = new SelectItem("Phổ Thông", "Phổ Thông");
				arrItem[1] = new SelectItem("Công Vụ", "Công Vụ");
				arrItem[2] = new SelectItem("Ngoại Giao", "Ngoại Giao");
				this.setListLoaiHC(arrItem);
			}
		}
		else {
			this.setNamSinh("");
			this.setCoQuan("");
			this.setChucVu("");
			this.setDangVien(false);
			this.listLoaiHC = null;			
			this.setError("");
		}
		return "finish_them_hochieu";
	}
	/**************************************
	 * Method name		: checkValue
	 * Return type		: boolean
	 * Description		:
	 * Created date		: Aug 22, 2008
	 * Author			: Ngoc Diem
	 **************************************/
	public boolean checkValue(){
		boolean result = false;
		String str ;
		if(this.ten == null || this.ten.equals("") ||
				this.cmnd == null || this.cmnd.equals("") || 
				this.namSinh == null || this.namSinh.equals("") || 
				this.coQuan == null || this.coQuan.equals("") ||
				this.chucVu == null || this.chucVu.equals("")||
				this.loaiHC == null || this.loaiHC.equals("")||
				this.noiCap == null || this.noiCap.equals("")){
			this.setError("Bạn phải nhập đầy đủ thông tin có dấu (*) phía trước ");
			result = false;
		}
		
		else {
			this.setError("");
			result = true;
		}
				
		return result;
	}
public void resetData(){
	this.ten =null;
	this.cmnd = null;
	this.chucVu = null;
	this.coQuan = null;
	this.dangVien = false;
	this.namSinh = null;	
	this.loaiHC = null;
	this.ngayCap = null;
	this.ngayHetHan =null;
	this.noiCap = null;
	this.moTa = null;
}
	/**************************************
	 * Method name		: taoHoChieu
	 * Return type		: String
	 * Description		:
	 * Created date		: Aug 20, 2008
	 * Author			: Ngoc Diem
	 **************************************/
	public String taoHoChieu(){
		if(this.checkValue() == true){
			String strSoHC = managerID.getNewID("hochieu");
			this.soHC = strSoHC;
			boolean boolGioiTinh = false;
			if (this.gioiTinh.equals("Nam")){
				boolGioiTinh = true;
			}
			else boolGioiTinh = false; 
		
			java.sql.Date dateNgayCap = DateUtils.utilDateToSqlDate(new java.util.Date());
			
			int month = DateUtils.LayThangHienTai();
			int day = DateUtils.LayNgayHienTai();
			int year = DateUtils.LayNamHienTai()+5;
			String strNgayHetHan = month+"-"+day+"-"+year;
			
			String sqlHoChieu = "insert into hochieu(sohc,loaihc,ngaycap,ngayhethan,noicap,cmnd) values('"+this.soHC+"','"+this.loaiHC+"','"+dateNgayCap+"','"+strNgayHetHan+"','"+this.noiCap+"','"+this.cmnd+"')";
			String traNhan = "";
			if(this.trangThaiHC.equals("Giao")) traNhan = "G";
			else traNhan = "N";
			String sqlViTriLuuTru = "insert into vitriluutru(vitritu,ngantrongtu,sohc, mota, trangthainhan) values('"+this.maTu+"','"+this.nganTu+"','"+this.soHC+"','"+this.moTa+"','"+traNhan+"')";
			System.out.println("SQL ho chieu "+sqlHoChieu);
			System.out.println("SQL Vi Tri luu tru "+sqlViTriLuuTru);
			
			//ca nhan nay co trong csdl roi
			if(this.checkCMNDInDatabase() == true){
				if(utils.insertData(sqlHoChieu) == 1 && utils.insertData(sqlViTriLuuTru) == 1){
					this.setError("Đã tạo xong hộ chiếu cho "+this.ten+". Số HC là "+this.soHC);
					this.resetData();
				}
				else this.setError("Việc tạo hộ chiếu xảy ra lỗi");
			}
			
			else {
				System.out.println("File Anh "+this.fileAnh);
				if(utils.insertCaNhan(this.cmnd,this.ten,this.coQuan,this.chucVu,this.dangVien,this.namSinh,boolGioiTinh,this.fileAnh) == 1 && utils.insertData(sqlHoChieu) == 1 && utils.insertData(sqlViTriLuuTru) == 1){
					this.setError("Đã tạo xong hộ chiếu cho "+this.ten+". Số HC là "+this.soHC);
					this.resetData();
				}
//				else this.setError("Việc tạo hộ chiếu xảy ra lỗi");
			}
		
		}
		return "finish_them_hochieu";
	}

	/**************************************
	 * Method name		: getListLoaiHC
	 * Return type		: SelectItem[]
	 * Description		:
	 * Created date		: Aug 20, 2008
	 * Author			: Ngoc Diem
	 **************************************/
	public SelectItem[] getListLoaiHC() {
		if(this.listLoaiHC == null) {
		SelectItem[] arrItem = new SelectItem[3];
		arrItem[0] = new SelectItem("Phổ Thông", "Phổ Thông");
		arrItem[1] = new SelectItem("Công Vụ", "Công Vụ");
		arrItem[2] = new SelectItem("Ngoại Giao", "Ngoại Giao");
		this.listLoaiHC = arrItem;
		}
		return this.listLoaiHC;
	}

	public void setListLoaiHC(SelectItem[] listLoaiHC) {
		this.listLoaiHC = listLoaiHC;
	}
	public String huyTaoHC(){
		this.resetData();
		this.error = null;
		return "finish_them_hochieu";
	}

		
//************************ Sua ho chieu ****************
	public int getIdViTriLuuTru() {
		return idViTriLuuTru;
	}

	public void setIdViTriLuuTru(int idViTriLuuTru) {
		this.idViTriLuuTru = idViTriLuuTru;
	}
	
	public String getStt() {
		return stt;
	}

	public void setStt(String stt) {
		this.stt = stt;
	}
	
	public void setListHC(List<HoChieu> listHC) {
		this.listHC = listHC;
	}

	int acc =1;
	public List<HoChieu> getListHC() {
		this.setError("");
		if(this.listHC == null) {
			acc =1;
			this.helpTimKiem();
		}
		return listHC;
	}
	String strSoHC ="";
	/**************************************
	 * Method name		: helpTimKiem
	 * Return type		: String
	 * Description		:
	 * Created date		: Aug 20, 2008
	 * Author			: Ngoc Diem
	 **************************************/
	public String helpTimKiem(){
		String result = "finish_sua_hochieu";		
		this.setError("");
		List<HoChieu> list = new ArrayList<HoChieu>();
		String SQL ="";
			if(acc == 0){
			SQL ="select * from hochieu as HC,vitriluutru as VTLL, canhan as CN where HC.sohc = VTLL.sohc and  HC.cmnd = CN.cmnd and HC.sohc like '%"+this.soHC+"%' and VTLL.sohc = '"+this.soHC+"'";
			}
			else if(acc ==1){
				SQL = "select * from hochieu as HC, canhan as CN, vitriluutru as VTLL where HC.cmnd = CN.cmnd and VTLL.sohc = HC.sohc";
			}
			else if(acc ==2) {
				String strSoHC ="";
				String strTen = "";
				String strCMND = "";
				String strNamSinh = "";
				boolean boolGioiTinh = false;
				String strCoQuan ="";
				String strChucVu = "";
				String strDateNgayCap = "";
				String strDateNgayHetHan = "";
				String strNoiCap = "";
				String strMoTa = "";
				String strTrangThaiHC = "";
			
				if(this.soHC != null){
					if(this.soHC.length() > 0)
					strSoHC ="%"+this.soHC+"%";
					else strSoHC = "%";
				}
				else strSoHC = "%";
	
				if(this.ten != null){
					if(this.ten.length() > 0)
						strTen = "%"+this.ten+"%";
					else strTen ="%";
				}
				else strTen ="%";
			
				if(this.cmnd != null) {
					if(this.cmnd.length() > 0)
						strCMND = "%"+this.cmnd+"%";
					else strCMND = "%";
				}
				else strCMND = "%";
				
				if(this.coQuan != null) {
					if(this.coQuan.length() > 0)
						strCoQuan = "%"+this.coQuan+"%";
					else strCoQuan = "%";
				}
				else strCoQuan = "%";
				
				if(this.chucVu != null) {
					if(this.chucVu.length() > 0)
						strChucVu = "%"+this.chucVu+"%";
					else strChucVu = "%";
				}
				else strChucVu = "%";
				
				if(this.cmnd != null) {
					if(this.cmnd.length() > 0)
						strCMND = "%"+this.cmnd+"%";
					else strCMND = "%";
				}
				else strCMND = "%";
				
				if(this.ngayCap != null)  {
					if(!this.ngayCap.equals(""))
						strDateNgayCap = "HC.ngaycap = '"+this.dateNgayCap+"' and ";
				}
				
				if(this.ngayHetHan != null) {
					if(!this.ngayHetHan.equals(""))
					strDateNgayHetHan  = "HC.ngayhethan = '"+this.dateNgayHetHan+"' and";
				}
				
				if(this.noiCap != null) {
					if(this.noiCap.length() > 0)
						strNoiCap = "%"+this.noiCap+"%";
					else strNoiCap = "%";
				}
				else strNoiCap = "%";
				
				if(this.moTa != null) {
					if(this.moTa.length() > 0)
						strMoTa = "%"+this.moTa+"%";
					else strMoTa = "%";
				}
				else strMoTa = "%";
				
				if(this.namSinh != null) {
					if(this.namSinh.length() > 0)
						strNamSinh = "%"+this.namSinh+"%";
					else strNamSinh = "%";
				}
				else strNamSinh = "%";
				
				if (this.gioiTinh.equals("Nam")){
					boolGioiTinh = true;
				}
				else boolGioiTinh = false; 
				
				if(this.trangThaiHC.equals("Giao")) strTrangThaiHC = "G";
				else strTrangThaiHC = "N";
							
				SQL ="select * from hochieu as HC,vitriluutru as VTLL, canhan as CN " +
				"where HC.cmnd = CN.cmnd and VTLL.sohc = HC.sohc and " +				
				"HC.sohc like '"+strSoHC+"' and " +
				"CN.ten like '"+strTen+"' and " +
				"CN.cmnd like '"+strCMND+"' and  "+
				"CN.namsinh like '"+strNamSinh+"' and "+
				"CN.gioitinh = "+boolGioiTinh+" and "+
				"CN.coquan like '"+strCoQuan+"' and "+
				"CN.chucvu like '"+strChucVu+"' and "+
				"HC.noicap like '"+strNoiCap+"' and "+
				"VTLL.mota like '"+strMoTa+"' and "+
				"VTLL.vitritu = '"+this.maTu+"' and "+
				"VTLL.trangthainhan = '"+strTrangThaiHC+"' and "+
				strDateNgayCap +
				strDateNgayHetHan+
				"VTLL.ngantrongtu like '"+this.nganTu+"' ";
				
			}
			else if(acc == 3){
				HttpServletRequest request = UtilsOnScopes.getRequest();
				String lnkSoHC = request.getParameter("lnkSoHC");
				SQL ="select * from hochieu as HC, canhan as CN, vitriluutru as VTLL where CN.cmnd = HC.cmnd and HC.sohc = VTLL.sohc and HC.sohc = '"+lnkSoHC+"'";
			}
		System.out.println("SQL "+SQL);
		ArrayList<HashMap<String, Object>> array = ConnectDB.getDataTypeList(SQL);
		if (array.size() > 0) {
			HashMap<String, Object> hash = new HashMap<String, Object>();
			int stt = 0;
			for (int i = 0; i < array.size(); i++) {
				
				//***********8 Dua thong tin vao danh sach doan ra o phia duoi
				stt = i + 1;
				hash = (HashMap<String, Object>) array.get(i);
				HoChieu HC = new HoChieu();
				String strSoHC1 = hash.get("sohc").toString();
				String strTen = hash.get("ten").toString();
				String strcmnd = hash.get("cmnd").toString();	
				String strcoquan = hash.get("coquan").toString();
				String strgioitinh = hash.get("gioitinh").toString();
				String strNamSinh = hash.get("namsinh").toString();
				HC.setStt(String.valueOf(stt));
				HC.setTen(strTen);
				HC.setSoHC(strSoHC1);
				HC.setCmnd(strcmnd);
				HC.setCoQuan(strcoquan);
				String gioitinh ="";
				boolean b = Boolean.parseBoolean(strgioitinh);
				if (b == true) gioitinh = "Nam";
				else gioitinh = "Nữ";
				HC.setGioiTinh(gioitinh);
				HC.setNamSinh(strNamSinh);
				
				//***************** Ket thuc dua thong tin ***************
				
				//******************* set thong tin len textbox
				if(array.size() == 1){
				strSoHC = strSoHC1;
				this.setIdViTriLuuTru(Integer.parseInt(hash.get("id").toString()));
				this.setSoHC(strSoHC1);	
				this.setTen(strTen);
				this.setCmnd(strcmnd);
				this.setNamSinh(strNamSinh);
				this.setGioiTinh(gioitinh);
				this.setCoQuan(strcoquan);
				this.setChucVu(hash.get("chucvu").toString());
				this.setDangVien(Boolean.parseBoolean(hash.get("dangvien").toString()));
				this.setLoaiHC(hash.get("loaihc").toString());
				this.setNgayCap(hash.get("ngaycap").toString());
				this.setNgayHetHan(hash.get("ngayhethan").toString());
				this.setNoiCap(hash.get("noicap").toString());
				this.setMaTu(hash.get("vitritu").toString());
				this.setNganTu(hash.get("ngantrongtu").toString());
				this.setMoTa(hash.get("mota").toString());
				this.setDateNgayCap(java.sql.Date.valueOf(hash.get("ngaycap").toString()));
				this.setDateNgayHetHan(java.sql.Date.valueOf(hash.get("ngayhethan").toString()));
				String strTrangThaiHC = hash.get("trangthainhan").toString();
				if(strTrangThaiHC.equals("N")) this.setTrangThaiHC("Nhận");
				else this.setTrangThaiHC("Giao");
				
				}
				else {
					this.resetData();
					this.setError(null);
					this.soHC = null;
				}
				//************* Ket thuc set thong tin len textbox
				list.add(HC);
				
			}
		}
		else {
			if(this.soHC != null){
			this.setError("Không tìm thấy hộ chiếu");
			this.resetData();
			}
			else this.soHC = null;
		}
		this.setListHC(list);
		return result;
	}
	public String displayAll(){
		acc =1;
		this.soHC =null;
		this.helpTimKiem();
		return "finish_sua_hochieu";
	}
	public String timKiemTheoSoHC(){
		acc =0;
		this.helpTimKiem();
		return "finish_sua_hochieu";
	}
	public String timKiemTheoLink(){
		acc =3;
		this.helpTimKiem();
		return "finish_sua_hochieu";
	}
	
	//tim kiem mo rong
	public String timKiemMoRong(){
		acc =2;
		this.helpTimKiem();
		return "finish_sua_hochieu";
	}
	
	public String capNhatHoChieu(){
		String userName = UtilsOnScopes.getUserName();
		//neu la admin thi ko can goi yeu cau
	if(!userName.equals("admin")){
		if(truyXuatDatabase.getDuyet(this.soHC) == -1) {
			this.setError("Bạn phải gửi yêu cầu đến người quản trị trước khi sửa hộ chiếu này!");
		}
		else if(truyXuatDatabase.getDuyet(this.soHC) == 0) {
			this.setError("Yêu cầu của bạn đang ở trạng thái chờ duyệt");
		}		
		else{
			System.out.println("str "+strSoHC);
		if(!strSoHC.equals(this.soHC)){
			this.setError("Không được thay đổi số hộ chiếu");
		}
		else {
			if(this.checkValue()){
			boolean boolGioiTinh = false;
			if(this.gioiTinh.equals("Nam")) boolGioiTinh = true;
			else boolGioiTinh = false;
			
			String strTraNhan = "";
			if(this.trangThaiHC.equals("Giao")) strTraNhan = "G";
			else strTraNhan = "N";
			String sqlUpdateHC = "update hochieu set loaihc = '"+this.loaiHC+"', " +
											" ngaycap = '"+this.dateNgayCap+"', " +
											" ngayhethan = '"+this.dateNgayHetHan+"'," +
											" noicap = '"+this.noiCap+"', " +
											" cmnd = '"+this.cmnd+"' where sohc = '"+this.soHC+"'";
			
			String sqlUpdateViTriLuuTru = "update vitriluutru set vitritu = '"+this.maTu+"', " +
													" ngantrongtu = '"+this.nganTu+"'," +
													" sohc = '"+this.soHC+"', " +
													" trangthainhan = '"+strTraNhan+"'," +
													" mota = '"+this.moTa+"' where id = "+this.idViTriLuuTru;
			String sqlUpdateCaNhan = "update canhan set ten = '"+this.ten+"', " +
												" namsinh = '"+this.namSinh+"'," +
												" gioitinh = "+boolGioiTinh+", " +
												" coquan = '"+this.coQuan+"'," +
//												" anh = ' "+"', " +
												" dangvien = "+ this.dangVien+", " +
												" chucvu  = '"+this.chucVu+"' where cmnd = '"+this.cmnd+"'";
			
				System.out.println("SQL "+sqlUpdateCaNhan);
				System.out.println(sqlUpdateHC);
				System.out.println(sqlUpdateViTriLuuTru);
			if(utils.capNhatData(sqlUpdateCaNhan) && utils.capNhatData(sqlUpdateHC) && utils.capNhatData(sqlUpdateViTriLuuTru)){	
				this.setError("Hộ chiếu "+this.soHC+" đã được cập nhật thành công");
				truyXuatDatabase.insertNhatKyXoaSua(UtilsOnScopes.getUserName(), DateUtils.utilDateToSqlDate(new java.util.Date()), this.soHC, "Sửa", "Sửa đối tượng này trong cssdl");
			}
			else this.setError("Quá trình cập nhật xảy ra lỗi");
		}
		}
		}
	}
	else {
		if(this.checkValue()){ 
		
		if(!strSoHC.equals(this.soHC)){
			this.setError("Không được thay đổi số hộ chiếu");
		}
		else {
			boolean boolGioiTinh = false;
			if(this.gioiTinh.equals("Nam")) boolGioiTinh = true;
			else boolGioiTinh = false;
			
			String strTraNhan = "";
			if(this.trangThaiHC.equals("Giao")) strTraNhan = "G";
			else strTraNhan = "N";
			String sqlUpdateHC = "update hochieu set loaihc = '"+this.loaiHC+"', " +
											" ngaycap = '"+this.dateNgayCap+"', " +
											" ngayhethan = '"+this.dateNgayHetHan+"'," +
											" noicap = '"+this.noiCap+"', " +
											" cmnd = '"+this.cmnd+"' where sohc = '"+this.soHC+"'";
			
			String sqlUpdateViTriLuuTru = "update vitriluutru set vitritu = '"+this.maTu+"', " +
													" ngantrongtu = '"+this.nganTu+"'," +
													" sohc = '"+this.soHC+"', " +
													" trangthainhan = '"+strTraNhan+"'," +
													" mota = '"+this.moTa+"' where id = "+this.idViTriLuuTru;
			String sqlUpdateCaNhan = "update canhan set ten = '"+this.ten+"', " +
												" namsinh = '"+this.namSinh+"'," +
												" gioitinh = "+boolGioiTinh+", " +
												" coquan = '"+this.coQuan+"'," +
//												" anh = ' "+"', " +
												" dangvien = "+ this.dangVien+", " +
												" chucvu  = '"+this.chucVu+"' where cmnd = '"+this.cmnd+"'";
			
				System.out.println("SQL "+sqlUpdateCaNhan);
				System.out.println(sqlUpdateHC);
				System.out.println(sqlUpdateViTriLuuTru);
			if(utils.capNhatData(sqlUpdateCaNhan) && utils.capNhatData(sqlUpdateHC) && utils.capNhatData(sqlUpdateViTriLuuTru)){	
				this.setError("Hộ chiếu "+this.soHC+" đã được cập nhật thành công");
				truyXuatDatabase.insertNhatKyXoaSua(UtilsOnScopes.getUserName(), DateUtils.utilDateToSqlDate(new java.util.Date()), this.soHC, "Sửa", "Sửa đối tượng này trong cssdl");
			}
			else this.setError("Quá trình cập nhật xảy ra lỗi");
		}
		}
	}
		return "finish_sua_hochieu";
		
	}

	public java.util.Date getDateNgayCap() {
		return dateNgayCap;
	}

	public void setDateNgayCap(java.util.Date dateNgayCap) {
		this.dateNgayCap = dateNgayCap;
	}

	public java.util.Date getDateNgayHetHan() {
		return dateNgayHetHan;
	}

	public void setDateNgayHetHan(java.util.Date dateNgayHetHan) {
		this.dateNgayHetHan = dateNgayHetHan;
	}

}
